﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data.SqlServerCe;
using System.Data;

using NetworkAssetManager.General;
using NetworkAssetManager.DataAccess;
using NetworkAssetManager.Entity;


namespace NetworkAssetManager.DataAccess
{
    public class DiscoverDb:BaseDB
    {
        public int CreateTable()
        {
            throw new System.NotImplementedException();
        }

        //UPDATE Discover SET LastChecked ='2009-10-25 14:29:39.063'WHERE MachineID =16;

        public DBCode UpdateLastScan ( int machineID, DateTime scanTime)
        {
            DBCode retCode = DBCode.Ok; 
            try
            {
                using (DbTransaction transaction = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = Connection.CreateCommand())
                    {
                        command.CommandText = @"
                            UPDATE Discover SET LastChecked = ? WHERE MachineID = ?";
                        command.Parameters.Add(new SqlCeParameter("LastChecked",
                            SqlDbType.DateTime)).Value = scanTime;
                        command.Parameters.Add(new SqlCeParameter("MachineID",
                            SqlDbType.Int, 4)).Value = machineID;

                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    retCode = DBCode.Ok; 
                }
            }
            catch (Exception)
            {
                retCode = DBCode.Error; 
            }
            return retCode; 
        }
        public DBCode InsertDiscover(EntDiscover discover)
        {
            DBCode retCode = DBCode.Ok; 
            try
            {
                using (DbTransaction transaction = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = Connection.CreateCommand())
                    {
                        command.CommandText = @"
                            INSERT INTO Discover    
                                (IPAddress, LastChecked, Discovered, StatusMessage, DomainName, MachineName, CredentialID)
                                 VALUES (?, ?, ?, ?, ?, ?, ?)";
                        command.Parameters.Add(new SqlCeParameter("IPAddress",
                            SqlDbType.NVarChar, 20)).Value = discover.IPAddr;
                        command.Parameters.Add(new SqlCeParameter("LastChecked",
                            SqlDbType.DateTime)).Value = DateTime.Now;
                        command.Parameters.Add(new SqlCeParameter("Discovered",
                            SqlDbType.Bit)).Value = discover.Discovered;
                        command.Parameters.Add(new SqlCeParameter("StatusMessage",
                            SqlDbType.NVarChar, 512)).Value = discover.StatusMessage;
                        command.Parameters.Add(new SqlCeParameter("DomainName",
                            SqlDbType.NVarChar, 200)).Value = discover.DomainName;
                        command.Parameters.Add(new SqlCeParameter("MachineName",
                            SqlDbType.NVarChar, 200)).Value = discover.MachineName;
                        command.Parameters.Add(new SqlCeParameter("CredentialID",
                            SqlDbType.Int, 4)).Value = discover.CredentialID;

                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    retCode = DBCode.Ok; 
                }
            }
            catch (Exception)
            {
                retCode = DBCode.Error; 
            }
            return retCode; 
        }

        public DBCode UpdateDiscover(EntDiscover discover)
        {
            DBCode retCode = DBCode.Ok;
            try
            {
                using (DbTransaction transaction = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = Connection.CreateCommand())
                    {
                        command.CommandText = @"
                            UPDATE Discover SET LastChecked = ?, Discovered = ?, StatusMessage = ? WHERE MachineID = ?";
                        
                        command.Parameters.Add(new SqlCeParameter("LastChecked",
                            SqlDbType.DateTime)).Value = DateTime.Now;
                        command.Parameters.Add(new SqlCeParameter("Discovered",
                            SqlDbType.Bit)).Value = discover.Discovered;
                        command.Parameters.Add(new SqlCeParameter("StatusMessage",
                            SqlDbType.NVarChar, 512)).Value = discover.StatusMessage;
                        command.Parameters.Add(new SqlCeParameter("MachineID",
                            SqlDbType.Int, 4)).Value = discover.MachineID;

                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    retCode = DBCode.Ok;
                }
            }
            catch (Exception)
            {
                retCode = DBCode.Error;
            }
            return retCode; 
        }

        public DBCode UpdateDiscover(List<EntDiscover> discoverMachList)
        {
            DBCode retCode = DBCode.Ok;

            try
            {
                using (DbTransaction transaction = Connection.BeginTransaction())
                {
                    foreach (EntDiscover discover in discoverMachList)
                    {
                        using (SqlCeCommand command = Connection.CreateCommand())
                        {

                            command.CommandText = @" UPDATE [Discover] SET    
                                IPAddress = ?, LastChecked = ?, Discovered = ?, StatusMessage = ?, DomainName = ?, MachineName = ?, CredentialID = ? WHERE MachineID = ?";
                            command.Parameters.Add(new SqlCeParameter("IPAddress",
                                SqlDbType.NVarChar, 20)).Value = discover.IPAddr;
                            command.Parameters.Add(new SqlCeParameter("LastChecked",
                                SqlDbType.DateTime)).Value = DateTime.Now;
                            command.Parameters.Add(new SqlCeParameter("Discovered",
                                SqlDbType.Bit)).Value = discover.Discovered;
                            command.Parameters.Add(new SqlCeParameter("StatusMessage",
                                SqlDbType.NVarChar, 512)).Value = discover.StatusMessage;
                            command.Parameters.Add(new SqlCeParameter("DomainName",
                                SqlDbType.NVarChar, 200)).Value = discover.DomainName;
                            command.Parameters.Add(new SqlCeParameter("MachineName",
                                SqlDbType.NVarChar, 200)).Value = discover.MachineName;
                            command.Parameters.Add(new SqlCeParameter("CredentialID",
                                SqlDbType.Int, 4)).Value = discover.CredentialID;
                            command.Parameters.Add(new SqlCeParameter("MachineID",
                                SqlDbType.Int, 4)).Value = discover.MachineID;

                            command.ExecuteNonQuery();
                        }
                    }
                    transaction.Commit();
                    retCode = DBCode.Ok;
                }

            }
            catch (Exception)
            {
                retCode = DBCode.Error; 
            }



            return retCode;
        }


        public DBCode DeleteDiscover(string machineName)
        {
            DBCode retCode = DBCode.Ok;
            try
            {
                using (DbTransaction transaction = Connection.BeginTransaction())
                {
                    using (SqlCeCommand command = Connection.CreateCommand())
                    {
                        command.CommandText = @"
                            DELETE Discover WHERE MachineName = ?;";
                        command.Parameters.Add(new SqlCeParameter("MachineName",
                            SqlDbType.NVarChar, 200)).Value = machineName;
                        command.ExecuteNonQuery();
                    }
                    transaction.Commit();
                    retCode = DBCode.Ok;
                }
            }
            catch (Exception)
            {
                retCode = DBCode.Error;
            }
            return retCode; 

        }


        public List<EntDiscover> GetDiscover(string machineName)
        {
            List<EntDiscover> list = new List<EntDiscover>();

            try
            {
                using (SqlCeCommand command = Connection.CreateCommand())
                {
                    if ( machineName != null)
                    {
                        command.CommandText = @"
                          SELECT * FROM [Discover] where MachineName = ?";

                        command.Parameters.Add(new SqlCeParameter("MachineName",
                            SqlDbType.NVarChar, 200)).Value = machineName;
                    }
                    else
                    {
                        command.CommandText = @"
                          SELECT * FROM [Discover] ";
                    }

                    using (SqlCeDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            list.Add(DataReaderToObject(reader));
                        }
                    }
                }
            }
            catch (Exception){}
            return list;
        }
        public EntDiscover GetMachineDetails(string machineName)
        {
            EntDiscover machine = null; 
            try
            {
                using (SqlCeCommand command = Connection.CreateCommand())
                {
                    command.CommandText = @"
                          SELECT * FROM [Discover] where MachineName = ?";
                    
                    command.Parameters.Add(new SqlCeParameter("MachineName",
                        SqlDbType.NVarChar, 200)).Value = machineName;

                    using (SqlCeDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            machine = DataReaderToObject(reader);
                        }
                    }
                }
            }
            catch (Exception) { }
            return machine;
        }
        private EntDiscover DataReaderToObject(SqlCeDataReader reader)
        {
            EntDiscover machine = new EntDiscover();

            machine.MachineID = reader.GetInt32(reader.GetOrdinal("MachineID"));
            machine.IPAddr = reader.GetString(reader.GetOrdinal("IPAddress"));
            machine.LastChecked = reader.GetDateTime(reader.GetOrdinal("LastChecked"));
            machine.Discovered = reader.GetBoolean(reader.GetOrdinal("Discovered"));
            machine.StatusMessage = reader.GetString(reader.GetOrdinal("StatusMessage"));
            machine.DomainName = reader.GetString(reader.GetOrdinal("DomainName"));
            machine.MachineName = reader.GetString(reader.GetOrdinal("MachineName"));
            machine.CredentialID = reader.GetInt32(reader.GetOrdinal("CredentialID"));

            return machine;
        }
    }
}
